﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Collections;
using System.Net.Mail;
using MeetingMinutesManager.DB;

namespace MeetingMinutesManager
{
    public class MOMOperator
    {
        public static void RefreshTreeView(TreeView trvMOMs, String userName, String role)
        {
            // Clear the nodes of the tree views
            trvMOMs.Nodes.Clear();

            // Add root node
            TreeNode root = new TreeNode("Root", "Root");
            trvMOMs.Nodes.Add(root);

            // Add Categories
            AddCategoryNodes(root, userName, role);
    
            // Add MOMs
            AddMOMNodes(root);
        }

        public static void AddMOMNodes(TreeNode root)
        {
            foreach (TreeNode categoryNode in root.ChildNodes)
            {
                String category = categoryNode.Value.ToString().Trim();
                DataTable dtMOMs = ReadMOMs(category);

                foreach (DataRow row in dtMOMs.Rows)
                {
                    if (row["title"] != null)
                    {
                        String title = row["title"].ToString().Trim();
                        String ID = row["ID"].ToString().Trim();

                        TreeNode momNode = new TreeNode(title, ID);

                        categoryNode.ChildNodes.Add(momNode);
                    }
                }
            }
        }

        public static void AddCategoryNodes(TreeNode root, String userName, String role)
        {
            DataTable dtCategories = ReadCategories(userName, role);
            foreach (DataRow row in dtCategories.Rows)
            {
                if (row["ID"] != null)
                {
                    String category = row["ID"].ToString();
                    String coldsc = row["coldsc"].ToString();
                    TreeNode categoryNode = new TreeNode(coldsc, category);

                    root.ChildNodes.Add(categoryNode);
                }
            }               
        }

        public static DataTable ReadCategories(String userName, String role)
        {
            String team = ResourceOperator.ReadTeam(userName);

            String strQueryCategories = "select mom_type.id, mom_type.coldsc " +
                " from mom_type " +
                " where mom_type.role <= " + role +
                " and (mom_type.team = " + team + " or mom_type.team = (select parent from team where team.id = " + team + ")  or mom_type.team in (select id from team where team.parent = " + team + ")) " +
                " order by coldsc";
            DBOperator localOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
            return localOperator.Read(strQueryCategories);
        }

        public static DataTable ReadMOMs(String category)
        {
            String strQueryMOMs = "select id, title from mom where category = '" + category + "' order by id";
            DBOperator localOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
            return localOperator.Read(strQueryMOMs);
        }

        public static DataTable ReadMOMForGridView(String momID)
        {
            DataTable dt = new DataTable();

            if (momID.Equals(String.Empty))
            {
                String strQueryMOM = "select * from mom where 1 = 2";
                DBOperator localOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
                dt = localOperator.Read(strQueryMOM);
            }
            else
            {
                String strQueryMOM = "select * from mom where id = " + momID + " order by id";
                DBOperator localOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
                dt = localOperator.Read(strQueryMOM);
            }

            return dt;
        }

        public static DataTable ReadActions(String momID)
        {
            DataTable dt = new DataTable();

            if (momID.Equals(String.Empty))
            {
                String strQueryAgenda = "select * from mom_action where status <> 'Closed' and 1 = 2";
                DBOperator localOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
                dt = localOperator.Read(strQueryAgenda);
            }
            else
            {
                String strQueryAgenda = "select * from mom_action where status <> 'Closed' and mom_id = " + momID + " order by id";
                DBOperator localOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
                dt = localOperator.Read(strQueryAgenda);
            }

            return dt;
        }

        public static void SendActionReminder(String momID)
        {
            DataTable dtActions = ReadActions(momID);

            Hashtable actionReminders = new Hashtable();
            foreach (DataRow row in dtActions.Rows)
            {
                if (row["owner"] != DBNull.Value)
                {
                    String owner = row["owner"].ToString().Trim();
                    String action = row["action"].ToString().Trim();
                    String status = row["status"].ToString().Trim();
                    String dueDate = row["duedate"].ToString().Trim();

                    if (actionReminders.ContainsKey(owner))
                    {
                        String actionBody = actionReminders[owner].ToString();
                        actionBody += "<b>Action</b>: " + action + " <b>Status</b>: " + status + " <b>Due Date</b>: " + dueDate + "\n <br><br>";
                        actionReminders[owner] = actionBody;
                    }
                    else
                    {
                        actionReminders.Add(owner, "<b>Action</b>: " + action + " <b>Status</b>: " + status + " <b>Due Date</b>: " + dueDate + "\n <br><br>");
                    }
                }
            }

            // Send Emails
            String subject = ReadMOMTitle(momID);
            foreach (String owner in actionReminders.Keys)
            {
                String message = actionReminders[owner].ToString().Trim();

                SendReminder(owner, subject, message);
            }
        }

        public static String ReadMOMTitle(String momID)
        {
            String strQuery = "select mom_type.coldsc + ' ' + CONVERT(CHAR(10), mom.meeting_date, 23) title from mom join mom_type on (mom.category = mom_type.ID) where mom.ID = " + momID;
            DBOperator localOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
            DataTable dtTitle = localOperator.Read(strQuery);

            String title = String.Empty;
            foreach (DataRow row in dtTitle.Rows)
            {
                title = row["title"].ToString().Trim();
            }

            title += " - Action Reminder";
            return title;
        }

        public static DataTable ReadActionOwners(String momID)
        {
            DataTable dt = new DataTable();

            if (momID.Equals(String.Empty))
            {
                String strQueryAgenda = "select distinct owner from mom_action where 1 = 2";
                DBOperator localOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
                dt = localOperator.Read(strQueryAgenda);
            }
            else
            {
                String strQueryAgenda = "select distinct owner from mom_action where mom_id = " + momID + " order by id";
                DBOperator localOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
                dt = localOperator.Read(strQueryAgenda);
            }

            return dt;
        }

        public static void SendReminder(String owner, String subject, String message)
        {
            MailAddressCollection sendTos = GetMailAddressCollection(owner);
            MailAddress ownerAddress = ResourceOperator.GetEmailAddress(owner);

            EmailOperator.Send(subject, message, sendTos, ownerAddress, true, null);
        }

        public static MailAddressCollection GetMailAddressCollection(String owner)
        {
            MailAddressCollection addressCollection = new MailAddressCollection();
            addressCollection.Add(new MailAddress("rain.wei@bleum.com"));

            MailAddress ownerAddress = ResourceOperator.GetEmailAddress(owner);
            addressCollection.Add(ownerAddress);

            return addressCollection;
        }

        /// <summary>
        /// Return true if the mom is a lead
        /// </summary>
        /// <param name="title"></param>
        /// <returns></returns>
        public static bool IsMoMExists(String category, String title)
        {
            bool isExists = false;

            String strQueryExits = "select count(id) exist from mom where category = " + category + " and title = '" + title + "'";
            DBOperator localOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
            DataTable dtExists = localOperator.Read(strQueryExits);

            foreach (DataRow row in dtExists.Rows)
            {
                if (row["exist"] != DBNull.Value)
                {
                    if (Convert.ToInt32(row["exist"]) >= 1)
                    {
                        isExists = true;
                    }
                }
            }

            return isExists;
        }

        /// <summary>
        /// Return true if the category is a lead
        /// </summary>
        /// <param name="category"></param>
        /// <returns></returns>
        public static bool IsCategoryExits(String category)
        {
            bool isExists = false;

            String strQueryExits = "select count(id) exist from mom_type where coldsc = '" + category + "'";
            DBOperator localOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
            DataTable dtExists = localOperator.Read(strQueryExits);

            foreach (DataRow row in dtExists.Rows)
            {
                if (row["exist"] != DBNull.Value)
                {
                    if (Convert.ToInt32(row["exist"]) >= 1)
                    {
                        isExists = true;
                    }
                }
            }

            return isExists;
        }

        public static void UpdateActionStatus(String status, String ID)
        {
            String strUpdate = "update mom_action set status = '" + status + "' where id = " + ID;
            DBOperator localOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
            localOperator.Update(strUpdate);
        }
    }
}
